﻿<%@ WebHandler Language="C#" Class="RemoteData" %>

using System;
using System.Web;
using Ext.Net;
using ExtSite;
using ExtSite.Classes;
using System.Collections.Generic;
using System.Linq;
using Microsoft.ApplicationBlocks.Data;
using System.Data.SqlClient;
using System.Data;
public class RemoteData : IHttpHandler
{

    private string _obID = null;
    private string _requestID = null;
    private string _connectString = null;
    private string _procSelect = "dbo.SelectDataWithPaging";
    public void ProcessRequest(HttpContext context)
    {
        this._connectString = global::System.Configuration.ConfigurationManager.ConnectionStrings["DatabaseConnectionString"].ConnectionString;
        this._obID          = context.Request["ob"];//HttpUtility.ParseQueryString(context.Request.UrlReferrer.Query)["ob"];
        this._requestID     = HttpUtility.ParseQueryString(context.Request.UrlReferrer.Query)["id"];
        string action       = context.Request["action"];
        StoreResponseData sr = new StoreResponseData();
        try
        {
            if (string.IsNullOrEmpty(action) || Store.Action(action) == StoreAction.Read)
            {
                if (!string.IsNullOrEmpty(context.Request["link"]))
                    sr = this.ReturnDataLine(context);
                else
                    sr = this.ReturnData(context);
                if (sr != null)
                    sr.Return();
                return;
            }
            StoreAction storeAction = Store.Action(action);
            var db = new DataConfigClassesDataContext();
            ExtSite.Classes.Object ob = db.Objects.SingleOrDefault(o => o.ID == int.Parse(this._obID));
            if (storeAction == StoreAction.Destroy)
            {
                this.DeleteData(context, ob);
            }
            else if (storeAction == StoreAction.Update)
            {
                this.UpdateData(context, ob);
            }
            else if (storeAction == StoreAction.Create)
            {
                this.InserData(context, ob);
            }
            sr = this.ReturnData(context);
        }
        catch (Exception e)
        {
            sr.Success = false;
            sr.Message = e.Message;
            if (e.InnerException != null)
                sr.Message = e.InnerException.Message;
        }
        sr.Return();
    }

    private StoreResponseData ReturnData(HttpContext context)
    {
        StoreResponseData sr = new StoreResponseData();
        try
        {
            if (string.IsNullOrEmpty(this._obID))
                return null;
            var db = new DataConfigClassesDataContext();

            string sSort = null;
            string pk = string.Empty;
            StoreRequestParameters prms = new StoreRequestParameters(context);


            ExtSite.Classes.Object ob = db.Objects.SingleOrDefault(o => o.ID == int.Parse(this._obID));
            string sRequestFilter = context.Request["filter"];
            string sFilter = string.Empty;
            if (!string.IsNullOrEmpty(sRequestFilter))
            {
                sFilter = this.getFilter(ob, sRequestFilter);
            }
            if (!string.IsNullOrEmpty(this._requestID))
            {
                sFilter = this.getFilter(ob, string.Format("{{\"ID\":\"={0}\"}}", this._requestID));
            }
            string query = string.Format(this._procSelect);
            string fields = string.Empty;

            foreach (Object_property op in ob.Object_properties)
            {
                if (op.PreKey != null && op.PreKey.Value == true)
                {
                    pk = op.Name;
                }
                fields += string.Format("[{0}],", op.Name);
            }

            //If there is no PK set first property is visual PK
            if (string.IsNullOrEmpty(pk))
            {
                pk = ob.Object_properties[0].Name;
            }

            fields = fields.Trim(',');
            if (prms.Sort != null && prms.Sort.Length > 0)
            {

                sSort = prms.Sort[0].Property + " " + prms.Sort[0].Direction;
            }
            else
                sSort = pk + " ASC";

            System.Data.DataSet dts = SqlHelper.ExecuteDataset(this._connectString, query, "" + ob.Name + "", pk, sSort, context.Request.QueryString["page"], context.Request.QueryString["limit"], fields, sFilter, null);

            sr.Data = JSON.Serialize(dts.Tables[0]);
            sr.Total = int.Parse(dts.Tables[1].Rows[0][0].ToString());
            sr.Success = true;
        }
        catch (Exception ex)
        {
            sr.Message = ex.Message;
            if (ex.InnerException != null)
                sr.Message = ex.InnerException.Message;
        }
        return sr;
    }
    private StoreResponseData ReturnDataLine(HttpContext context)
    {
        StoreResponseData sr = new StoreResponseData();
        try
        {
            if (string.IsNullOrEmpty(this._obID))
                return null;
            var db = new DataConfigClassesDataContext();

            string sSort = null;
            string pk = string.Empty;
            StoreRequestParameters prms = new StoreRequestParameters(context);


            ExtSite.Classes.Object ob = db.Objects.SingleOrDefault(o => o.ID == int.Parse(this._obID));
            string sRequestFilter = context.Request["filter"];
            string sFilter = string.Empty;
            if (!string.IsNullOrEmpty(sRequestFilter))
            {
                sFilter = this.getFilter(ob, sRequestFilter);
            }
            string sLink = context.Request["link"];
            string sCard = context.Request["card"];
            if (!string.IsNullOrEmpty(sLink))
            {
                sFilter = this.getFilter(ob, string.Format("{{\"{0}\":\"={1}\"}}", sLink, this._requestID));
            }
            string query = string.Format(this._procSelect);
            string fields = string.Empty;

            foreach (Object_property op in ob.Object_properties)
            {
                if (op.PreKey != null && op.PreKey.Value == true)
                {
                    pk = op.Name;
                }
                fields += string.Format("[{0}],", op.Name);
            }

            //If there is no PK set first property is visual PK
            if (string.IsNullOrEmpty(pk))
            {
                pk = ob.Object_properties[0].Name;
            }

            fields = fields.Trim(',');
            if (prms.Sort != null && prms.Sort.Length > 0)
            {

                sSort = prms.Sort[0].Property + " " + prms.Sort[0].Direction;
            }
            else
                sSort = pk + " ASC";

            System.Data.DataSet dts = SqlHelper.ExecuteDataset(this._connectString, query, "" + ob.Name + "", pk, sSort, context.Request.QueryString["page"], context.Request.QueryString["limit"], fields, sFilter, null);

            sr.Data = JSON.Serialize(dts.Tables[0]);
            sr.Total = int.Parse(dts.Tables[1].Rows[0][0].ToString());
            sr.Success = true;
        }
        catch (Exception ex)
        {
            sr.Message = ex.Message;
            if (ex.InnerException != null)
                sr.Message = ex.InnerException.Message;
        }
        return sr;
    }
    public bool IsReusable
    {
        get
        {
            return false;
        }
    }
    public void InserData(HttpContext context, ExtSite.Classes.Object ob)
    {
        try
        {
            StoreDataHandler dataHandler = new StoreDataHandler(context);
            List<JsonObject> jsonO = dataHandler.ObjectData<JsonObject>();
            foreach (JsonObject jo in jsonO)
            {
                string sQuery = string.Empty;
                string sCulumn = string.Empty;
                string sValues = string.Empty;
                List<SqlParameter> param = new List<SqlParameter>();
                foreach (Object_property op in ob.Object_properties)
                {
                    if (op.PreKey != null && op.PreKey.Value == true)
                    {
                        continue;
                    }
                    else if (jo.Keys.Contains(op.Name))
                    {
                        sCulumn += string.Format("[{0}],", op.Name);
                        sValues += string.Format("@{0},", op.Name);
                        SqlParameter p = new SqlParameter(op.Name, jo[op.Name]);
                        param.Add(p);
                    }
                }
                sQuery = string.Format("INSERT INTO [{0}]({1}) VALUES ({2}); SELECT select @@IDENTITY", ob.Name, sCulumn.Trim(','), sValues.Trim(','));
                object returnValue = SqlHelper.ExecuteScalar(this._connectString, CommandType.Text, sQuery, param.ToArray());
            }
        }
        catch
        {
            throw;
        }
    }
    public void UpdateData(HttpContext context, ExtSite.Classes.Object ob)
    {
        try
        {
            StoreDataHandler dataHandler = new StoreDataHandler(context);
            List<JsonObject> jsonO = dataHandler.ObjectData<JsonObject>();
            foreach (JsonObject jo in jsonO)
            {
                if (!string.IsNullOrEmpty(this._requestID))
                {
                    if (jo.ContainsKey("id"))
                    {
                        jo["id"] = this._requestID;
                    }
                    else
                    {
                        jo.Add("id", this._requestID);
                    }
                }
                string sQuery = string.Format("UPDATE [{0}] SET ", ob.Name);
                string sWhere = string.Empty;
                List<SqlParameter> param = new List<SqlParameter>();
                foreach (Object_property op in ob.Object_properties)
                {

                    if (op.PreKey != null && op.PreKey.Value == true)
                    {
                        sWhere = string.Format(" WHERE [{0}] = @{0}", op.Name);
                    }
                    else if (jo.Keys.Contains(op.Name) || jo.Keys.Contains(op.Name.ToLower()))
                    {
                        sQuery += string.Format("[{0}] = @{0},", op.Name);
                    }
                    if (jo.Keys.Contains(op.Name.ToLower()))
                    {
                        SqlParameter p = new SqlParameter(op.Name, jo[op.Name.ToLower()]);
                        //p.SqlDbType = (SqlDbType)Enum.Parse(typeof(SqlDbType), op.Type);
                        param.Add(p);
                    }
                    else if (jo.Keys.Contains(op.Name))
                    {
                        SqlParameter p = new SqlParameter(op.Name, jo[op.Name]);
                        //p.SqlDbType = (SqlDbType)Enum.Parse(typeof(SqlDbType), op.Type);
                        param.Add(p);
                    }

                }
                sQuery = sQuery.Trim(',') + " " + sWhere;
                SqlHelper.ExecuteNonQuery(this._connectString, CommandType.Text, sQuery, param.ToArray());
            }

        }
        catch
        {
            throw;
        }
    }
    public void DeleteData(HttpContext context, ExtSite.Classes.Object ob)
    {
        try
        {
            StoreDataHandler dataHandler = new StoreDataHandler(context);
            List<JsonObject> jsonO = dataHandler.ObjectData<JsonObject>();
            foreach (JsonObject jo in jsonO)
            {
                string sQuery = string.Format("DELETE [{0}]  ", ob.Name);
                string sWhere = string.Empty;
                List<SqlParameter> param = new List<SqlParameter>();
                foreach (Object_property op in ob.Object_properties)
                {
                    if (op.PreKey != null && op.PreKey.Value == true)
                    {
                        sWhere = string.Format(" WHERE [{0}] = @{0}", op.Name);
                        SqlParameter p = new SqlParameter(op.Name, jo[op.Name]);
                        param.Add(p);
                        break;
                    }

                }
                sQuery = sQuery.Trim(',') + " " + sWhere;
                SqlHelper.ExecuteNonQuery(this._connectString, CommandType.Text, sQuery, param.ToArray());
            }

        }
        catch
        {
            throw;
        }
    }

    public string getFiterSyntax(ExtSite.Classes.Object ob, string propertyname, string filter, bool exact, string operation)
    {
        string result = string.Empty;
        if (string.IsNullOrEmpty(operation))
        {
            operation = " = ";
            exact = false;
        }
        if (operation == "=")
        {
            operation = " = ";
        }
        try
        {
            string type = string.Empty;
            Object_property op = ob.Object_properties.SingleOrDefault(p => p.Name == propertyname);
            if (op != null)
            {
                type = op.Type;
            }
            else
                return string.Empty;
            switch (type)
            {
                case "int":
                case "decimal":
                    if (string.IsNullOrEmpty(filter))
                        return "";
                    if (exact)
                        result = result + string.Format(" [{0}] {2} '{1}' ", propertyname, filter, operation);
                    else
                        result = result + string.Format(" [{0}] like '%{1}%' ", propertyname, filter);
                    break;
                case "datetime":
                case "date":
                    if (exact)
                    {
                        result = result + string.Format(" [{0}] {2} '{1}' ", propertyname, filter, operation);
                    }
                    else
                    {
                        result = result + string.Format(" [{0}] {2} '{1}' ", propertyname, filter, operation);
                    }
                    break;
                case "bit":
                    if (string.IsNullOrEmpty(filter))
                        return "";
                    result = result + string.Format(" [{0}] {2} {1} ", propertyname, filter, operation);
                    break;
                default:
                    if (filter.ToLower().Trim() != "null" && exact == true)
                    {
                        filter = string.Format("\"[{0}]\"", filter);
                    }

                    if (exact)
                        result = result + string.Format(" [{0}] {2} '{1}' ", propertyname, filter, operation);
                    else
                        result = result + string.Format(" [{0}] like '%{1}%' ", propertyname, filter);
                    break;
            }
        }
        catch (Exception ex)
        { }
        return result;
    }
    public string getFilter(ExtSite.Classes.Object ob, string filter)
    {
        string result = string.Empty;
        try
        {
            if (!string.IsNullOrEmpty(filter))
            {
                JsonObject obj = (JsonObject)JSON.Deserialize(filter, typeof(JsonObject));
                foreach (string k in obj.Keys)
                {
                    if (obj[k] != null && !string.IsNullOrEmpty(obj[k].ToString()))
                    {
                        try
                        {
                            string syntax = "";
                            string firststring = obj[k].ToString().Substring(0, 1);
                            if (obj[k].ToString().Contains(".."))
                            {
                                int index = obj[k].ToString().IndexOf("..");
                                syntax = this.getFiterSyntax(ob, k, obj[k].ToString().Substring(0, index), false, string.Empty);
                                if (!string.IsNullOrEmpty(syntax))
                                    result = result + syntax + " OR ";
                                syntax = this.getFiterSyntax(ob, k, obj[k].ToString().Substring(index + 2), false, string.Empty);
                                if (!string.IsNullOrEmpty(syntax))
                                    result = result + syntax + " AND ";

                            }
                            else if (obj[k].ToString().Contains("|"))
                            {
                                int index = obj[k].ToString().IndexOf("|");
                                string[] values = obj[k].ToString().Split('|');
                                result = result + " ( ";
                                for (int i = 0; i < values.Length; i++)
                                {
                                    if (i == values.Length - 1)
                                    {
                                        syntax = this.getFiterSyntax(ob, k, values[i], false, string.Empty);
                                        if (!string.IsNullOrEmpty(syntax))
                                            result = result + syntax + ") AND ";
                                    }
                                    else
                                    {
                                        syntax = this.getFiterSyntax(ob, k, values[i], false, string.Empty);
                                        if (!string.IsNullOrEmpty(syntax))
                                            result = result + syntax + " OR ";
                                    }
                                }

                            }
                            else if (firststring == "!" || firststring == ">" || firststring == "<" || firststring == "=")
                            {
                                string seconstring = obj[k].ToString().Substring(1, 1);
                                if (seconstring == ">" && firststring == "<")
                                {
                                    firststring = "!";
                                    seconstring = "=";
                                }
                                if (seconstring != "=")
                                {
                                    seconstring = "";
                                }

                                //result = result + k + firststring + seconstring  + obj[k].ToString().Substring(firststring) + " AND ";
                                string filterValue = obj[k].ToString().Substring(firststring.Length + seconstring.Length);
                                if (filterValue == "''")
                                {
                                    syntax = this.getFiterSyntax(ob, k, "", true, firststring + seconstring);
                                    if (!string.IsNullOrEmpty(syntax))
                                        result = result + syntax + " OR ";
                                    syntax = this.getFiterSyntax(ob, k, "NULL", true, firststring + seconstring);
                                    if (!string.IsNullOrEmpty(syntax))
                                        result = result + syntax + " AND ";
                                }
                                else
                                {
                                    syntax = this.getFiterSyntax(ob, k, filterValue, true, firststring + seconstring);
                                    if (!string.IsNullOrEmpty(syntax))
                                        result = result + syntax + " AND ";
                                }

                            }
                            else
                            {
                                syntax = this.getFiterSyntax(ob, k, obj[k].ToString(), false, string.Empty);
                                if (!string.IsNullOrEmpty(syntax))
                                    result = result + syntax + " AND ";
                            }

                        }
                        catch (Exception)
                        {
                            continue;
                        }
                    }
                }
            }
            return result.Trim().TrimEnd('D', 'N', 'A');
        }
        catch (Exception ex)
        {
            //this.WriteLogs("Posted_Transfer_Shipments", "Posted_Transfer_Shipments_getFilter", ex);
            //NotificateMessage("Error", ex.Message);
            return string.Empty;
        }

    }
   
}